package com.shsh.safemanage.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;
import java.util.TreeMap;

import com.shsh.safemanage.model.JobPost;
import com.shsh.safemanage.model.JobTitle;
import com.shsh.safemanage.model.ManageGroup;
import com.shsh.safemanage.model.PostCheck;
import com.shsh.safemanage.util.ValueUtil;

public class TitlePostManageGroupDao {
	public JobPost getJobPostByID(Long id) {
		if (id == null) {
			return null;
		}
		
		String sql = "select job_post.ID, DEPARTMENT1_ID, DEPARTMENT2_ID, POST_NAME, JOB_TITLE_ID, ROLE_TYPE " +
				"from job_post left outer join role on job_post.ROLE_ID=role.ID where job_post.ID=?";
		
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, id);
			
			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				JobPost jobPost = new JobPost();
				jobPost.setId(rs.getLong("ID"));
				jobPost.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				jobPost.setDepartment2ID(rs.getLong("DEPARTMENT2_ID"));
				jobPost.setPostName(rs.getString("POST_NAME"));
				jobPost.setJobTitleID(rs.getInt("JOB_TITLE_ID"));
				jobPost.setRoleType(rs.getInt("ROLE_TYPE"));
				
				return jobPost;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return null;
	}
	
	public JobTitle getTitleByID(Long id) {
		String sql = "select ID, TITLE_TYPE, TITLE_LEVEL, TITLE_NAME from job_title where ID=?";
		
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, id);
			
			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				JobTitle jobTitle = new JobTitle();
				jobTitle.setId(rs.getLong("ID"));
				jobTitle.setTitleType(rs.getInt("TITLE_TYPE"));
				jobTitle.setTitleLevel(rs.getInt("TITLE_LEVEL"));
				jobTitle.setTitleName(rs.getString("TITLE_NAME"));
				
				return jobTitle;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return null;
	}
	
	public ManageGroup getManageGroup(Long id) {
		String sql = "select * from manage_group where id=?";
		
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, id);
			
			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				ManageGroup manageGroup = new ManageGroup();
				manageGroup.setId(ValueUtil.getLong(rs.getLong("ID")));
				manageGroup.setManageGroupName(ValueUtil.getString(rs.getString("MANAGE_GROUP_NAME")));
				
				return manageGroup;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return null;
	}
	
	public void setPostManageGroupRelation(Long postID, Long manageGroupID) {
		String sql = "update job_post set MANAGE_GROUP_ID=? where ID=?";
		
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			conn.setAutoCommit(false);
			
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, manageGroupID);
			pstm.setLong(2, postID);
			
			pstm.executeUpdate();
			
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
	}
	
	public void setPostTitleRelation(Long postID, Long titleID) {
		String sql = "update job_post set JOB_TITLE_ID=? where ID=?";
		
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			conn.setAutoCommit(false);
			
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, titleID);
			pstm.setLong(2, postID);
			
			pstm.executeUpdate();
			
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
	}
	
	public List<ManageGroup> getManageGroupList() {
		String sql = "select ID, MANAGE_GROUP_NAME from manage_group";
		List<ManageGroup> manageGroupList = new ArrayList<ManageGroup>();
		
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				ManageGroup manageGroup = new ManageGroup();
				manageGroup.setId(rs.getLong("ID"));
				manageGroup.setManageGroupName(rs.getString("MANAGE_GROUP_NAME"));
				
				manageGroupList.add(manageGroup);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return manageGroupList;
	}
	
	public ManageGroup getManageGroupByID(Long id) {
		String sql = "select ID, MANAGE_GROUP_NAME from manage_group where ID=?";
		
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, id);
			
			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				ManageGroup manageGroup = new ManageGroup();
				manageGroup.setId(rs.getLong("ID"));
				manageGroup.setManageGroupName(rs.getString("MANAGE_GROUP_NAME"));
				
				return manageGroup;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return null;
	}
	
	public boolean addManagegroupPostcheckMapping(Long manageGroupID, Set<PostCheck> postcheckSet) {
		String selectSQL = "select ID from managegroup_postcheck_mapping where MANAGE_GROUP_ID=? and POSTCHECK_ID=?";
		String sql = "insert into managegroup_postcheck_mapping(MANAGE_GROUP_ID, POSTCHECK_ID) values(?, ?)";
		
		Connection conn = null;
		PreparedStatement pstm = null;
		PreparedStatement pstm1 = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(selectSQL);
			pstm1 = conn.prepareStatement(sql);
			for (PostCheck postcheck : postcheckSet) {
				pstm.setLong(1, manageGroupID);
				pstm.setLong(2, postcheck.getId());
				
				ResultSet rs = pstm.executeQuery();
				if (!rs.next()) {
					pstm1.setLong(1, manageGroupID);
					pstm1.setLong(2, postcheck.getId());
					
					pstm1.executeUpdate();
				}
			}
			
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return false;
	}
}
